import sqlite3
import os
from http.server import HTTPServer, BaseHTTPRequestHandler
import json
from urllib.parse import urlparse, parse_qs

# 数据库配置
DB_NAME = 'family_tree.db'

# 初始化数据库
def init_db():
    if not os.path.exists(DB_NAME):
        conn = sqlite3.connect(DB_NAME)
        cursor = conn.cursor()

        # 创建家族成员表
        cursor.execute('''
        CREATE TABLE members (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            gender TEXT NOT NULL,
            birth_date TEXT,
            death_date TEXT,
            biography TEXT,
            parent_id INTEGER,
            FOREIGN KEY (parent_id) REFERENCES members (id)
        )
        ''')

        # 创建家族事件表
        cursor.execute('''
        CREATE TABLE events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            description TEXT,
            date TEXT,
            member_id INTEGER,
            FOREIGN KEY (member_id) REFERENCES members (id)
        )
        ''')

        # 创建家族媒体表
        cursor.execute('''
        CREATE TABLE media (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            type TEXT NOT NULL,
            url TEXT NOT NULL,
            member_id INTEGER,
            FOREIGN KEY (member_id) REFERENCES members (id)
        )
        ''')

        # 创建荣誉表
        cursor.execute('''
        CREATE TABLE honors (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            description TEXT,
            date TEXT,
            member_id INTEGER,
            FOREIGN KEY (member_id) REFERENCES members (id)
        )
        ''')

        # 创建耻辱表
        cursor.execute('''
        CREATE TABLE disgraces (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            description TEXT,
            date TEXT,
            member_id INTEGER,
            FOREIGN KEY (member_id) REFERENCES members (id)
        )
        ''')

        # 插入示例数据
        cursor.execute("INSERT INTO members (name, gender, birth_date) VALUES (?, ?, ?)",
                      ('John Doe', 'Male', '1970-01-01'))
        cursor.execute("INSERT INTO members (name, gender, birth_date) VALUES (?, ?, ?)",
                      ('Jane Doe', 'Female', '1975-05-15'))

        conn.commit()
        conn.close()
        print(f'Database {DB_NAME} created and initialized.')
    else:
        print(f'Database {DB_NAME} already exists.')

# 数据库操作函数
def get_all_members():
    print('get_all_members() function called')
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM members')
    members = [dict(row) for row in cursor.fetchall()]
    print(f'Fetched {len(members)} members from database')
    conn.close()
    return members

def get_member_by_id(member_id):
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM members WHERE id = ?', (member_id,))
    member = dict(cursor.fetchone()) if cursor.fetchone() else None
    conn.close()
    return member

def add_member(member_data):
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute('''
    INSERT INTO members (name, gender, birth_date, death_date, biography, parent_id)
    VALUES (?, ?, ?, ?, ?, ?)
    ''', (
        member_data.get('name'),
        member_data.get('gender'),
        member_data.get('birth_date'),
        member_data.get('death_date'),
        member_data.get('biography'),
        member_data.get('parent_id')
    ))
    member_id = cursor.lastrowid
    conn.commit()
    conn.close()
    return member_id

def get_media_by_member(member_id=None):
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    if member_id:
        cursor.execute('SELECT * FROM media WHERE member_id = ?', (member_id,))
    else:
        cursor.execute('SELECT * FROM media')
    media = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return media

def get_honors():
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM honors')
    honors = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return honors

def get_disgraces():
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM disgraces')
    disgraces = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return disgraces

# HTTP请求处理器
class FamilyTreeHandler(BaseHTTPRequestHandler):
    def _set_headers(self, status_code=200):
        self.send_response(status_code)
        self.send_header('Content-type', 'application/json')
        self.send_header('Access-Control-Allow-Origin', '*')
        self.send_header('Access-Control-Allow-Methods', 'GET, POST, OPTIONS')
        self.send_header('Access-Control-Allow-Headers', 'Content-Type')
        self.end_headers()

    def do_OPTIONS(self):
        self._set_headers()

    def do_GET(self):
        parsed_path = urlparse(self.path)
        path = parsed_path.path
        query_params = parse_qs(parsed_path.query)

        if path == '/api/members':
            members = get_all_members()
            self._set_headers()
            self.wfile.write(json.dumps(members).encode())
        elif path.startswith('/api/members/'):
            try:
                member_id = int(path.split('/')[-1])
                member = get_member_by_id(member_id)
                if member:
                    self._set_headers()
                    self.wfile.write(json.dumps(member).encode())
                else:
                    self._set_headers(404)
                    self.wfile.write(json.dumps({'error': 'Member not found'}).encode())
            except ValueError:
                self._set_headers(400)
                self.wfile.write(json.dumps({'error': 'Invalid member ID'}).encode())
        elif path == '/api/media':
            member_id = query_params.get('member_id', [None])[0]
            if member_id:
                try:
                    member_id = int(member_id)
                    media = get_media_by_member(member_id)
                except ValueError:
                    self._set_headers(400)
                    self.wfile.write(json.dumps({'error': 'Invalid member ID'}).encode())
                    return
            else:
                media = get_media_by_member()
            self._set_headers()
            self.wfile.write(json.dumps(media).encode())
        elif path == '/api/honors':
            honors = get_honors()
            self._set_headers()
            self.wfile.write(json.dumps(honors).encode())
        elif path == '/api/disgraces':
            disgraces = get_disgraces()
            self._set_headers()
            self.wfile.write(json.dumps(disgraces).encode())
        else:
            self._set_headers(404)
            self.wfile.write(json.dumps({'error': 'Endpoint not found'}).encode())

    def do_POST(self):
        parsed_path = urlparse(self.path)
        path = parsed_path.path

        content_length = int(self.headers['Content-Length'])
        post_data = self.rfile.read(content_length)
        try:
            data = json.loads(post_data)
        except json.JSONDecodeError:
            self._set_headers(400)
            self.wfile.write(json.dumps({'error': 'Invalid JSON'}).encode())
            return

        if path == '/api/members':
            member_id = add_member(data)
            self._set_headers(201)
            self.wfile.write(json.dumps({'id': member_id}).encode())
        else:
            self._set_headers(404)
            self.wfile.write(json.dumps({'error': 'Endpoint not found'}).encode())

# 启动服务器
def run_server(port=3000):
    init_db()
    server_address = ('', port)
    httpd = HTTPServer(server_address, FamilyTreeHandler)
    print(f'Starting server on port {port}...')
    try:
        httpd.serve_forever()
    except KeyboardInterrupt:
        pass
    httpd.server_close()
    print('Server stopped.')

if __name__ == '__main__':
    run_server()